import pymysql

class DbHandle:

    def __init__(self,host='localhost', user='root', password="123456",
                       database='crm', port=3306):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port

    #建立数据库连接,返回connect对象
    def get_connect(self):
        try:
            conn = pymysql.connect(host=self.host, user=self.user,
                                   password= self.password,
                                   database=self.database, port=self.port)
            return conn
        except Exception as e:
            print(e,'连接错误')

    # 查询
    def search_data(self,*args):
        try:
            #获取连接
            self.conn=self.get_connect()
            #获取游标
            self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)
            # 执行SQL
            if len(args)==1:
                self.cursor.execute(args[0]) #只有sql语句
            elif len(args)==2:
                self.cursor.execute(args[0],args[1]) #sql语句，有参数的list
            res=self.cursor.fetchall()   #获取所有数据
            #提交
            self.conn.commit()
            return res
        except Exception as e:
            self.conn.rollback() #回滚事物
            print(e,'db operation error')
        finally:    #无论是否报错，都关闭游标和连接
            self.cursor.close()
            self.conn.close()


    #修改
    def update_data(self,sql):
        try:
            # 获取连接
            self.conn = self.get_connect()
            # 获取游标
            self.cursor = self.conn.cursor()
            self.cursor.execute(sql)  # 执SQL,返回影响行数
            # 提交
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()  # 回滚事物
            print(e,'db operation error')
        finally:  # 无论是否报错，都关闭游标和连接
            self.cursor.close()
            self.conn.close()


# if __name__ == '__main__':
#     abhandle=DbHandle(database='crm')
#     print(abhandle.update_data("delete from user_info where user_name='张三' and user_id=250"))
    # print(abhandle.search_data('select * from user_info'))